Water Collection System in Mexico City - 2022

Abstract

Rainwater harvesting is a sustainable and effective way to manage water resources in Mexico. By promoting water conservation and increasing water security, these systems offer a path towards a more water-resilient future.

Rainwater harvesting systems are a prominent water collection method in Mexico, especially in areas facing water scarcity.

These systems, called “Sistemas de Captación de Agua de Lluvia” (SCALL) offer several benefits:

Reduced strain on aquifers: By collecting rainwater, these systems decrease reliance on groundwater sources, which are often overexploited.
Improved water security: Especially in peri-urban areas, rainwater harvesting provides a more dependable water source, particularly during dry spells.
Cost-effective solution: Rainwater harvesting can significantly reduce water bills for households.
Empowering communities: Programs like “Aqua a tu Casa” in Mexico City train women to install and maintain these systems, fostering economic opportunities.

Watch the video

Environment setting

Show code

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import altair as alt
import duckdb as db
import folium
from folium.plugins import Fullscreen
from itables import init_notebook_mode
init_notebook_mode(all_interactive=True)
import warnings
warnings.filterwarnings('ignore')
plt.style.use('ggplot')

init_notebook_modetrusted

Show code

conn = db.connect('my_database.db')

Show code

df = conn.sql('select * from water_collection').df()

Show code

conn.close()

Show code

df.head()

expediente	pueblo	scall_col	territorial	fecha_instalacion	capacidad	alcaldia	colonia_datos	latitud	longitud
Loading ITables v2.2.4 from the `init_notebook_mode` cell... (need help?)

Dataset preview

Show code

# Dataset preview
from itables import show
df.columns = ['file', 'village', 'scall', 'territory', 'facility_date', 'capacity', 'alcaldia', 'suburb',
              'latitude','longitude']

show(df, lengthMenu=[10, 25, 50, 100,])

file	village	scall	territory	facility_date	capacity	alcaldia	suburb	latitude	longitude
Loading ITables v2.2.4 from the `init_notebook_mode` cell... (need help?)

Descriptive Statitistics

Show code

df.describe(include='number')

	capacity	latitude	longitude
Loading ITables v2.2.4 from the `init_notebook_mode` cell... (need help?)

Show code

df['facility_date'] = pd.to_datetime(df['facility_date'])

Show code

start = df['facility_date'].min().strftime('%d/%m/%Y')

Show code

end = df['facility_date'].max().strftime('%d/%m/%Y')

Show code

print(f'The research period comprehends from {start} until {end}.')

The research period comprehends from 02/05/2022 until 30/11/2022.

Installed Capacity by Alcaldia

Show code

alcaldias = (
    df.groupby('alcaldia')['capacity']
        .agg('sum')
        .to_frame()
        .reset_index()
        .sort_values('capacity')
)

Show code

alcaldias['capacity'] = alcaldias['capacity']/1_000_000

Show code

alt.Chart(alcaldias).mark_bar(size=50, color='#760000', filled=True).encode(
    y=alt.Y('alcaldia:N').sort('-x'),
    x=alt.X("capacity:Q").title('Capacity (million of liters)'),
    tooltip=['alcaldia', 'capacity'],
).properties(
    width=780,
    height=450,
    title='Installed Capacity by Alcaldia',
).configure_axisY(
    labelFontSize=14,
    titleFontSize=16,
    title='',
).configure_axisX(
    grid=True,
    labelFontSize=16,
    titleFontSize=16,
    #title='Capacity (million of liters)',
).configure_title(
    fontSize=20,
    font='Helvetica Neue',
).configure_view(
    fill='#ececec',
)

Installed Capacity by Village

Show code

villages = (
    df.groupby('village')['capacity']
        .agg('sum')
        .to_frame()
        .reset_index()
        .sort_values('capacity')
)

Show code

villages['capacity'] = villages['capacity']/1_000_000

Show code

alt.Chart(villages).mark_bar(size=10, color='#760000', filled=True).encode(
    y=alt.Y('village:N').sort('-x'),
    x=alt.X("capacity:Q").title('Capacity (million of liters)'),
    tooltip=['village', 'capacity'],
).properties(
    width=780,
    height=500,
    title='Installed Capacity by Village',
).configure_axisY(
    labelFontSize=9,
    titleFontSize=14,
    title='',
).configure_axisX(
    grid=True,
    labelFontSize=16,
    titleFontSize=16,
    #title='Capacity (million of liters)',
).configure_title(
    fontSize=20,
    font='Helvetica Neue',
).configure_view(
    fill='#ececec',
)

Installed Capacity by Month

Show code

months = df.groupby(pd.Grouper(key='facility_date', freq="ME"))['capacity'].sum().to_frame().reset_index()

Show code

months['capacity'] = months['capacity']/1_000_000

Show code

months.plot(x='facility_date', y='capacity', legend=None, figsize=(12,6), color='#800020')
plt.title('Installed Capacity for Water Collection by Month', fontsize=(18))
plt.ylabel('Capacity (million of liters)')
plt.xlabel('')
plt.show()

Villages with Installations

Show code

zones = (
    df.groupby('village')[['capacity','latitude','longitude']]
        .agg({'capacity':'sum','latitude':'mean','longitude':'mean'})
        .reset_index(drop=False)
)

Show code

#zones.plot.scatter(x='latitude',y='longitude', figsize=(12,7))
#plt.title('Location of Villages with Installations', fontsize=16)
#plt.show()

Show code

map = folium.Map(
    location=[19.26, -99.11],
    zoom_start=11.49,
    control_scale=False,
)
# Layers
Zones = folium.FeatureGroup(name='<u><b>Instalacion</b></u>', show=True)
map.add_child(Zones)
#draw marker with symbol you want at base
my_symbol_css_class= """ <style>
.fa-mysymbol3:before {
    font-family: Arial; 
    font-weight: bold;
    font-size: 12px;
    color: white;
    background-color:'';
    border-radius: 10px; 
    white-space: pre;
    content: 'P';
    }
</style>    
        """
# the below is just add above  CSS class to folium root map      
map.get_root().html.add_child(folium.Element(my_symbol_css_class))
# then we just create marker and specific your css class in icon like below
for i in zones.index:
   html=f"""
        <p style="font-size: 14px;">{zones.iloc[i]['village']}</font></p>
        <p style="font-size: 14px;">Capacity: {zones.iloc[i]['capacity']}</font></p>
        """
   iframe = folium.IFrame(html=html, width=200, height=130)
   popup = folium.Popup(iframe, max_width=250)
   folium.Marker(
        location = [zones.iloc[i]['latitude'], zones.iloc[i]['longitude']],
        icon = folium.Icon(color='darkred', prefix='fa', icon='fa-mysymbol3'),
        popup = popup,
        tooltip = zones.iloc[i]['village']
    ).add_to(Zones)
folium.plugins.Fullscreen().add_to(map)

<folium.plugins.fullscreen.Fullscreen at 0x31269f020>

Show code

map

Make this Notebook Trusted to load map: File -> Trust Notebook

Info

You can hover over the icons on the map to get further information and zoom in or out.

References

Portal de Datos Abiertos Ciudad de México. Sedema.
Dataset

Contact

Jesus LM
Economist & Data Scientist

Medium | Linkedin | Twitter